#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "


-- 插入数据到表内
insert into hive.edu_online_dws.dws_attendance
with signin_table as(
-- 打卡统计
select
    class_date,
    class_id,
    student_id,
        -- 考勤判断 正常出勤0 or 迟到1 or 其他2
    if(sum(case when
            cast(signin_time as time) >= date_add('minute', -40, cast(morning_begin_time as time)) and
            cast(signin_time as time) <= cast(morning_end_time as time)
            then 1 --> 上午打卡了
            else 0 --> 上午打卡了
        end)>0, --> 上午有打卡为true，上午没打卡为false
        if(
            sum(case when
                    cast(signin_time as time) >= date_add('minute', -40, cast(morning_begin_time as time)) and
                    cast(signin_time as time) <= date_add('minute', 10, cast(morning_begin_time as time))
                    then 1 --> 正常出勤了
                    else 0 --> 迟到了
                end)>0, --> 正常出勤了为true，迟到为false
            0,1),--> 正常出勤了为0，迟到为1
        2) as morning_sign_status, --> 上午没打卡为2
    if(sum(case when
            cast(signin_time as time) >= date_add('minute', -40, cast(afternoon_begin_time as time)) and
            cast(signin_time as time) <= cast(afternoon_end_time as time)
            then 1 --> 上午打卡了
            else 0 --> 上午打卡了
        end)>0, --> 上午有打卡为true，上午没打卡为false
        if(
            sum(case when
                    cast(signin_time as time) >= date_add('minute', -40, cast(afternoon_begin_time as time)) and
                    cast(signin_time as time) <= date_add('minute', 10, cast(afternoon_begin_time as time))
                    then 1 --> 正常出勤了
                    else 0 --> 迟到了
                end)>0, --> 正常出勤了为true，迟到为false
            0,1),--> 正常出勤了为0，迟到为1
        2) as afternoon_sign_status, --> 下午没打卡为2
    if(sum(case when
            cast(signin_time as time) >= date_add('minute', -40, cast(evening_begin_time as time)) and
            cast(signin_time as time) <= cast(evening_end_time as time)
            then 1 --> 上午打卡了
            else 0 --> 上午打卡了
        end)>0, --> 上午有打卡为true，上午没打卡为false
        if(
            sum(case when
                    cast(signin_time as time) >= date_add('minute', -40, cast(evening_begin_time as time)) and
                    cast(signin_time as time) <= date_add('minute', 10, cast(evening_begin_time as time))
                    then 1 --> 正常出勤了
                    else 0 --> 迟到了
                end)>0, --> 正常出勤了为true，迟到为false
            0,1),--> 正常出勤了为0，迟到为1
        2) as evening_sign_status --> 晚上没打卡为2

from hive.edu_online_dwb.dwb_signin_detail
group by class_date,class_id,student_id
having student_id is not null
),
leave_table as (
-- 迟到统计
select
    class_date,
    class_id,
    student_id,

    -- 请假判断 请假记为1，否则为0
    max(if(begin_time <= concat(class_date,' ',morning_begin_time) and
        end_time >= concat(class_date,' ',morning_end_time) ,
        1,0)) as morning_leave_status,
    max(if(begin_time <= concat(class_date,' ',afternoon_begin_time) and
        end_time >= concat(class_date,' ',afternoon_end_time) ,
        1,0)) as afternoon_leave_status,
    max(if(begin_time <= concat(class_date,' ',evening_begin_time) and
        end_time >= concat(class_date,' ',evening_end_time) ,
        1,0)) as evening_leave_status
from hive.edu_online_dwb.dwb_leave_detail
group by class_date,class_id,student_id
),
union_all as(
select
    class_date,
    class_id,
    student_id,
    morning_sign_status,
    afternoon_sign_status,
    evening_sign_status,
    0 as morning_leave_status,
    0 as afternoon_leave_status,
    0 as evening_leave_status
from signin_table
union all
select
    class_date,
    class_id,
    student_id,
    2 as morning_sign_status,
    2 as afternoon_sign_status,
    2 as evening_sign_status,
    morning_leave_status,
    afternoon_leave_status,
    evening_leave_status
from leave_table
)
select
    u.class_date,
    u.class_id,
    sum(if(u.morning_sign_status = 0, 1, 0)) as morning_attendance_count,
    sum(if(u.afternoon_sign_status = 0, 1, 0)) as afternoon_attendance_count,
    sum(if(u.evening_sign_status = 0, 1, 0)) as evening_attendance_count,
    sum(if(u.morning_sign_status = 1, 1, 0)) as morning_late_count,
    sum(if(u.afternoon_sign_status = 1, 1, 0)) as afternoon_late_count,
    sum(if(u.evening_sign_status = 1, 1, 0)) as evening_late_count,
    sum(if(u.morning_leave_status = 1, 1, 0)) as morning_leave_count,
    sum(if(u.afternoon_leave_status = 1, 1, 0)) as afternoon_leave_count,
    sum(if(u.evening_leave_status = 1, 1, 0)) as evening_leave_count,
    max(c.studying_student_count)-sum(if(u.morning_sign_status = 0, 1, 0))-sum(if(u.morning_sign_status = 1, 1, 0))-sum(if(u.morning_leave_status = 1, 1, 0))as morning_absent_count,
    max(c.studying_student_count)-sum(if(u.afternoon_sign_status = 0, 1, 0))-sum(if(u.afternoon_sign_status = 1, 1, 0))-sum(if(u.morning_leave_status = 1, 1, 0)) as afternoon_absent_count,
    max(c.studying_student_count)-sum(if(u.evening_sign_status = 0, 1, 0))-sum(if(u.evening_sign_status = 1, 1, 0))-sum(if(u.morning_leave_status = 1, 1, 0)) as evening_absent_count,
    max(c.studying_student_count) as studying_student_count
from union_all u
left join hive.edu_online_dwd.dim_class_studying_student_count c
    on c.class_id = u.class_id and c.studying_date = u.class_date
group by u.class_date, u.class_id;"